USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0015_TIPO_AFILIADO_BASE]    Script Date: 11/23/2011 17:47:00 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_M0015_TIPO_AFILIADO_BASE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_M0015_TIPO_AFILIADO_BASE]
GO

USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0015_TIPO_AFILIADO_BASE]    Script Date: 11/23/2011 17:47:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[WASP_M0015_TIPO_AFILIADO_BASE](@FECHA_INICIO CHAR(8), @FECHA_TERMINO AS CHAR(8))
AS
BEGIN
	select	CRTRAPEN = 
			case
				when(a.ctccli =3)then 'T'
				when(a.ctccli =4)then 'P'
				else 'A'
			END,
			rtrim(ltrim(tra.pfndoc))  as RUTTRABAJA
	into	dbo.tipo_afiliado
	from 	fsd010 cre (nolock),
			fsd601 cuo (nolock),
			fsr008 rel (nolock), 
			fsd002 tra (nolock),	
			fsd008 a  (nolock),
        	JT73109 coloc (nolock)
	where 	cre.aocta = cuo.ppcta and
      		cre.aooper = cuo.ppoper and
      		cre.aosuc = cuo.ppsuc and 
			cuo.ppsbop = 0 and 
			cuo.d601co= 'S' and
           	cre.aocta = rel.ctnro and 
      		rel.pendoc = tra.pfndoc and
      		cre.aosbop = 0 and
      		cre.aocta <> 999999999 and
     		coloc.JT73109FAP between @FECHA_INICIO AND @FECHA_TERMINO and 
			rel.ctnro = a.ctnro and 
			a.ctccli in(3,4,5) and
			cre.Aooper = coloc.JT73109FPA and
       		cre.Aosuc = coloc.JT73109SUC and
			NOT EXISTS(
				SELECT	*
        		FROM	as_desistidos as anu
        		WHERE	cre.aocta = anu.hcta and
						cre.aooper = anu.hoper and
						cre.aosuc = anu.hsucur
			)
	group by	cre.aosuc, cre.aooper, cre.aoimp, 
				cre.aotasa, cre.aotope,
				cre.aofval, tra.pfndoc,
        		tra.Pffnac, tra.Pfcant,a.ctccli
END

GO

